Delta Updates

The Enhanced build process allows Full Delta Extracts that perform append, updates and deletes to an existing FastStats data table without requiring a full extract and re-load of the data.

The fundamental difference is that instead of extracting the current state of a table delta tables are built by extracting a list of transactions that are to be applied in extracted order to build up the current state. The existing incremental extract process is used to extract a growing transaction log from a single query.

Transactions are applied in extracted order. This makes the order transactions are presented to Designer critical to achieve the correct build result.

If changes are made to the design that result in an incompatibility with the existing .dat files (data types, code size, selector coding scheme, etc.) then the delta processing will delete the existing .dat files and the table will be rebuilt from the full list of extracted transactions.

To avoid the transaction log growing too large periodically (weekly, monthly) the source should be compacted to remove updates and deletes, and the extract file deleted to force a full re-extraction.

Specify data using an incremental Custom Query and a specified ERN field. The query must return the candidate key field (URN) used and a delete flag that specifies the row update action. This is the best method to choose for tables that can have CRUD changes.

Every table that allows updates/deletes must have a FastStats Reference variable defined on it (URN).

The Delete Flag can be specified as a nullable numeric (tinyint/smallint/int/bigint). A value of 1 means that the record is to be deleted. Note that by default fields of type 'bit' are extracted as T/F values and so aren't suitable.

Basic Full Delta Example

Example extract query:

Copy

SELECT [DeleteFlag],[URN],[Surname],[Date of Birth],[ERN]
FROM [PeopleTransactions] WHERE [ERN] > @ERN

ERN Field: [ERN]

Delete Flag Field: [DeleteFlag]

Initial extract table (@ERN = -1):

DeleteFlag	URN	Surname	Date of Birth	ERN
NULL	1	Hosen	19861104	100
NULL	2	George	19640717	100
NULL	3	Cruden	19650807	100
NULL	4	Baxter	19570922	100

Delta extract appended to above table (@ERN = 100):

DeleteFlag	URN	Surname	Date of Birth	ERN
NULL	3	Wilson	NULL	200
NULL	5	McIntosh	19581025	200
1	4	NULL	NULL	300

Resulting table:

URN	Surname	Date of Birth
1	Hosen	19861104
2	George	19640717
3	Wilson	NULL
5	McIntosh	19581025

Update set rules

An append or update row must include the whole row.
For delete rows only the Delete Flag and URN columns are used, other columns are ignored.
If there are multiple rows for a URN then only the last row in the result set will be taken.
URN column must be defined as a Reference field in FastStats.
Delete Flag must be defined as a Numeric (Byte) and the Delete Flag set. This variable can go into the ‘Hidden’ folder.

How are Delta tables specified in Designer?

The table must be configured as an incremental extract with an ERN field defined.
A Delete Flag must be specified on the table.
The Delete Flag must be specified as a Numeric ‘Byte’ variable and the ‘Delete Flag’ variable property must be set to True.
Place the Delete Flag variable into a 'Hidden' folder so the end-user does not see it.

What should I see in the build log?

You should see entries similar to these giving the progress of the delta build and then the delta statistics once the build has finished:

What happens if I change the design?

The previous system .vie and .val files are compared to the new versions. This includes the variables built, the data types and the coding scheme. If any of these are incompatible or the previous .vie file is missing, then the entire table will be rebuilt from scratch from the full transaction extract file.

Rebuilding from Scratch

Deltas are extracted as incremental data sources and stored as check points. Usually only the latest checkpoint will be applied to the system but if the system requires a rebuild from scratch then the entire delta set will be processed and loaded.

Delta Stats

After each delta set is applied the following statistics are reported:

Appends	Number of records with URNs that don't exist in the system. E.g. A transaction record where the Delete Flag is not set and the URN is not in the system.
Deletes	Number of deletes with URNs that exist in the system. E.g. A transaction record where the Delete Flag is set to 1 and the URN is in the system.
Ignored Deletes	Number of deletes with URNs that don’t exist in the system. E.g. A transaction record where the Delete Flag is set to 1 but the URN does not exist in the system.
Skipped	Number of records in the delta set that have a more recent transaction. E.g. If there were 3 transactions in a delta set only the last is processed so there would be 2 skipped transactions.
Updates	Number of records with URNs that already exist in the system. E.g. A transaction record where the Delete Flag is not set and the URN is in the system.

Example Delta Data Source

The Training Data table [PeopleDelta] contains a version of the Bookings table divided up into 5 sets of delta transaction records. These sets are defined by the field 'ERN' which ranges from 0 to 4. There is also a Delete Flag which must be set to enable the Delta processing on this table.

Each delta set extracts a different number of records:

Each set contains appends/updates/deletes as follows:

After all 5 delta sets have been applied there will be 78037 People records in the People table, even though 168257 transaction records have been extracted.